from datetime import datetime, date
from decimal import Decimal

from sqlalchemy import DateTime, BigInteger, Integer, SmallInteger, DECIMAL
from sqlalchemy.dialects.mysql import INTEGER, BIGINT, SMALLINT
from sqlmodel import SQLModel, Field
from sqlmodel import (
    Column,
    String,
    CHAR
)

UnsignedInt = Integer()
UnsignedSmallInt = SmallInteger()
UnsignedBigInt = BigInteger()
UnsignedInt = UnsignedInt.with_variant(INTEGER(unsigned=True), 'mysql')
UnsignedSmallInt = UnsignedSmallInt.with_variant(SMALLINT(unsigned=True), 'mysql')
UnsignedBigInt = UnsignedBigInt.with_variant(BIGINT(unsigned=True), 'mysql')

class BaseCommonModel(SQLModel):
    # 创建时间
    create_time: datetime = Field(
        default_factory=datetime.now,
        sa_type=DateTime,
        description="创建时间"
    )
    # 修改时间
    update_time: datetime = Field(
        sa_type=DateTime,
        description="修改时间",
        default_factory=datetime.now,
        sa_column_kwargs={"onupdate": datetime.now},
       )


class BaseMixin:
    """model的基类,所有model都必须继承"""
    id: int | None = Field(sa_type=UnsignedBigInt, primary_key=True, default=None)
    create_time: datetime = Field(
        default_factory=datetime.now,
        sa_type=DateTime,
        description="创建时间"
    )
    # 修改时间
    update_time: datetime = Field(
        sa_type=DateTime,
        description="修改时间",
        default_factory=datetime.now,
        sa_column_kwargs={"onupdate": datetime.now},
       )


class DataCollectionCommunity(BaseMixin, SQLModel):
    province: str = Field(max_length=150, description="省份")
    area: str = Field(max_length=150, description="城市")
    county: str = Field(max_length=150, description="区域", nullable=True)
    province_id: int = Field(sa_type=UnsignedBigInt, description="省份ID", nullable=True)
    area_id: int = Field(sa_type=UnsignedBigInt, description="城市ID", nullable=True)
    county_id: int = Field(sa_type=UnsignedBigInt, description="区域ID", nullable=True)
    sector: str = Field(max_length=150, description="板块", nullable=True)
    community_name: str = Field(max_length=150, description="小区名称")
    collect_community_id: str = Field(max_length=100, nullable=False, unique=True, description="采集到的小区id")
    address: str = Field(max_length=255, description="地址", nullable=True)
    lon: Decimal = Field(max_digits=20, decimal_places=17, description="坐标经度", nullable=True)
    lat: Decimal = Field(max_digits=20, decimal_places=17, description="坐标纬度", nullable=True)
    construction_year: str | None = Field(max_length=50, description="建筑年代", nullable=True)
    residential_type: str | None = Field(max_length=50, description="住宅类型:板楼、塔楼等", nullable=True)
    property_fee: str | None = Field(max_length=100, description="物业费", nullable=True)
    property_company: str | None = Field(max_length=100, description="物业公司", nullable=True)
    developer: str | None = Field(max_length=120, description="开发商")
    data_source: int = Field(default=1, sa_type=UnsignedBigInt,
                             description="数据来源:1-采集,0-购买")  # Assuming UnsignedInt is non-negative integer
    common_id: int = Field(sa_type=UnsignedBigInt, description="公共表id", nullable=True)


class DataCollectionBuilding(BaseMixin, SQLModel):
    community_id: str = Field(max_length=100, nullable=False, description="采集到的小区id")
    collect_building_id: str = Field(max_length=100, nullable=False, unique=True, description="采集到的楼栋id")
    regulatory_banks: str = Field(default=None, max_length=100, description="监管银行", nullable=True)
    regulatory_account: str = Field(default=None, max_length=100, description="监管账号", nullable=True)
    certificate_no: str = Field(default=None, max_length=100, description="证书编号", nullable=True)
    building_number: str = Field(max_length=100, description="楼栋号")

class DataCollectionUnit(BaseMixin, SQLModel):
    generate_unit_id: str= Field(sa_type=CHAR(32), nullable=False, unique=True, default="生成的单元id")
    community_id: str = Field(max_length=100, nullable=False, description="采集到的小区id")
    building_id: str = Field(max_length=100, nullable=False, description="采集到的楼栋id")
    unit_number: str = Field(max_length=50, nullable=False, description="单元号")
    total_floor: int = Field(sa_type=SmallInteger ,description="总楼层")
    room_number_start: str = Field(max_length=100, nullable=False, description="起始房号")
    room_number_end: str = Field(max_length=100, nullable=False, description="结束房号")

class DataCollectionRoom(BaseMixin, SQLModel):
    community_id: str = Field(max_length=100, nullable=False, description="采集到的小区id")
    building_id: str = Field(max_length=100, nullable=False, description="采集到的楼栋id")
    unit_id: str = Field(sa_type=CHAR(32), nullable=False, description="单元id")
    collect_room_id: str = Field(max_length=100, unique=True, description="采集到的小区房间id")
    room_number: str = Field(max_length=50, description="房间号")
    floor: int = Field(description="楼层")
    building_area: Decimal = Field(default=0, max_digits=11, decimal_places=2, description="建筑面积(㎡)" ,nullable=True)
    inner_area: Decimal = Field(default=0, max_digits=11, decimal_places=2, description="套内面积(㎡)", nullable=True)
    public_area: Decimal = Field(default=0, max_digits=11, decimal_places=2, description="公摊面积(㎡)", nullable=True)
    purpose: str = Field(max_length=20, description="房屋用途-住宅等", nullable=True)
    sale_status: str = Field(max_length=20, description="销售状态", nullable=True)
    filing_time: date = Field(description="备案时间", nullable=True)
